package com.alibaba.otter.canal.parse.driver.mysql;

import com.alibaba.fastjson.JSON;
import com.alibaba.otter.canal.parse.driver.mysql.packets.server.FieldPacket;
import com.alibaba.otter.canal.parse.driver.mysql.packets.server.OKPacket;
import com.alibaba.otter.canal.parse.driver.mysql.packets.server.ResultSetPacket;

import java.io.IOException;
import java.net.InetSocketAddress;
import java.util.List;

/**
 * CREATE TABLE `user` (
 *   `id` int(11) NOT NULL AUTO_INCREMENT,
 *   `name` varchar(18) NOT NULL,
 *   `password` varchar(15) NOT NULL,
 *   PRIMARY KEY (`id`)
 * ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
 *
 * mysql> select * from t_user;
 * +----+---------------+----------+
 * | id | name          | password |
 * +----+---------------+----------+
 * |  1 | tianshozhi    | xx       |
 * |  2 | wangxiaoxiiao | yy       |
 * +----+---------------+----------+
 */
public class testMysqlConnection {
    public static void main(String[] args) {
        // testMysqlConnection();
    }

    public void testMysqlConnection(){
        MysqlConnector connector = new MysqlConnector();
        try {
            //1 创建数据库连接
            connector = new MysqlConnector();
            // 设置数据库ip、port
            connector.setAddress(new InetSocketAddress("127.0.0.1", 3306));
            // 设置用户名
            connector.setUsername("root");
            // 设置密码
            connector.setPassword("your password");
            // 设置默认连接到的数据库
            connector.setDefaultSchema("test");
            // 设置链接字符串,33表示UTF-8
            connector.setCharsetNumber((byte) 33);
            // ======设置网络相关参数===========
            // 设置socket超时时间，默认30s，也就是发送一个请求给mysql时，如果30s没响应，则会抛出SocketTimeoutException
            connector.setSoTimeout(30 * 1000);
            // 设置发送缓冲区发小，默认16K
            connector.setSendBufferSize(16 * 1024);// 16K
            // 设置接受缓冲区大小，默认16K
            connector.setReceiveBufferSize(16 * 1024);// 16k
            //调用connect方法建立连接
            connector.connect();
            //2 ... do something....
        }catch (IOException e){
            e.printStackTrace();
        }finally {
            try {
                //关闭链接
                connector.disconnect();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    public void testQuery() throws IOException {
        MysqlConnector connector = new MysqlConnector();
        try {
            //1 创建数据库连接
            connector = new MysqlConnector();
            connector.setAddress(new InetSocketAddress("127.0.0.1", 3306)); // 设置数据库ip、port
            connector.setUsername("root");  // 设置用户名
            connector.setPassword("your password"); // 设置密码

            //1 创建数据库连接
            connector.connect();
            //2 构建查询执行器，并执行查询
            MysqlQueryExecutor executor = new MysqlQueryExecutor(connector);
            //ResultSetPacket作用类似于ResultSet
            ResultSetPacket result = executor.query("select * from test.user");
            //3 对查询结果进行解析
            //FieldPacket中封装的字段的一些源信息，如字段的名称，类型等
            List<FieldPacket> fieldDescriptors = result.getFieldDescriptors();
            //字段的值使用String表示，jdbc编程中使用的getInt，getBoolean，getDate等方法，实际上都是都是字符串转换得到的
            List<String> fieldValues = result.getFieldValues();
            //打印字段名称
            for (FieldPacket fieldDescriptor : fieldDescriptors) {
                String fieldName = fieldDescriptor.getName();
                System.out.print(fieldName + "  ");
            }
            //打印字段的值
            System.out.println("\n" + fieldValues);
            // id  name  password
            // [1, tianshozhi, xx, 2, wangxiaoxiiao, yy]
        } finally {
            connector.disconnect();
        }
    }

    public void testUpdate() {
        MysqlConnector connector = new MysqlConnector();
        try {
            //1 创建数据库连接
            connector = new MysqlConnector();
            connector.setAddress(new InetSocketAddress("127.0.0.1", 3306)); // 设置数据库ip、port
            connector.setUsername("root");  // 设置用户名
            connector.setPassword("your password"); // 设置密码


            connector.connect();
            MysqlUpdateExecutor executor = new MysqlUpdateExecutor(connector);
            OKPacket okPacket = executor.update("insert into test.user(name,password) values('tianbowen','zzz')");
            System.out.println(JSON.toJSONString(okPacket,true));
            // {
            //    "affectedRows":"AQ==",
            //    "fieldCount":0,
            //    "insertId":"AQ==",
            //    "message":"",
            //    "serverStatus":2,
            //    "warningCount":0
            //}
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                connector.disconnect();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }


}
